package d1026;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.*;
import java.util.Map;
import java.util.List;

@SuppressWarnings("all")
public class DBHelper {
    /**
     * 数据库操作助手类, 简化数据库操作, 通过简单的输入SQL语句和参数, 执行并返回结果
     */


    // 驱动类路径
    protected static String driver;
    // 连接串
    protected static String url;
    // 用户名
    protected static String user;
    // 密码
    protected static String password;
    static {
        try{
            //初始化数据库配置参数
            Properties prop = new Properties();//MAP子类
            //获取类加载驱动
            ClassLoader classLoader = DBHelper.class.getClassLoader();
            //读取项目根目录的配置文件
            InputStream in = classLoader.getResourceAsStream("jdbc.properties");

            if (in==null){
                throw new IOException("配置文件不存在!");
            }
            prop.load(in);
            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            user = prop.getProperty("username");
            password = prop.getProperty("password");
            Class.forName(driver);
        }catch (ClassNotFoundException e){
            throw new RuntimeException("数据库加载失败",e);
        }catch (IOException e){
            throw new RuntimeException("数据库配置文件jdbc.properties 加载失败！");
        }
    }
    public static  void main(String[] args) throws SQLException{
       // List<Map<String,Object>> list = selectListMap("select*from emp where empno > ?",7788);
        //for (Map<String,Object> row:list){
          //  System.out.println(row);
        Map<String,Object> row = selectOneMap("select*from emp where empno = ?",7788);
        System.out.println(row);
        row = selectOneMap("select*from emo where empno = ?",9999);
        System.out.println(row);
        try{
            row = selectOneMap("select*from emp where empno > ?",7788);
        }catch(Exception e){
            e.printStackTrace();
            System.out.println("这是正确处理方式");
        }
    }

    /**
     * 获取连接
     */
    public static Connection getConnection() {

        try{
            return DriverManager.getConnection(url,user,password);
        }catch (Exception e){
            throw new RuntimeException(e);
        }

    }
    /**
     * 更新数据库
     */
    public static int update(String sql, Object... params) throws SQLException {
        try(Connection conn = getConnection();){
            PreparedStatement ps = conn.prepareStatement(sql);
            for (int i = 0;i<params.length;i++){
                ps.setObject(i+1,params[i]);
            }
            return ps.executeUpdate();
        }
    }

    /**
     * 查询多行数据, 返回List<Map>集合
     */
    public static List<Map<String, Object>> selectListMap(String sql, Object... params) throws SQLException {
        System.out.println("SQL:"+sql);
        System.out.println("参数"+Arrays.toString(params));
      try(Connection conn = getConnection();){
          //构建语句
          PreparedStatement ps = conn.prepareStatement(sql);
          //设置参数
          for (int i=0;i<params.length;i++){
              ps.setObject(i+1,params[i]);
          }
          ResultSet rs = ps.executeQuery();
          //返回结果集"元数据"对象
          ResultSetMetaData rsmd = rs.getMetaData();
          //返回结果集中列的数量
          int columnCount = rsmd.getColumnCount();
          //创建返回的结果集
          List<Map<String,Object>> list = new ArrayList<>();
          while (rs.next()){
              //创建Map集合
              Map<String, Object> row = new LinkedHashMap<>();
              for (int i = 0;i<columnCount;i++) {
                  String columnName = rsmd.getColumnName(i + 1);
                  Object columnValue = rs.getObject(i+1);
                  row.put(columnName,columnValue);
              }
              //将该行数据对象存入list集合
              list.add(row);
          }
          return list;
      }
    }

    /**
     * 查询单行数据, 返回Map集合
     */
    public static Map<String, Object> selectOneMap(String sql, Object... params) throws SQLException {
        try(Connection conn = getConnection();) {
            PreparedStatement ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            for (int i = 0; i < params.length; i++) {
                ps.setObject(i + 1, params);
            }
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            if (rs.next() == false) {
                return null;
            }
            if (rs.next()) {
                throw new IllegalStateException("结果集数量大于1");
            }
            rs.previous();

            //创建返回的集合对象
            Map<String, Object> row = new LinkedHashMap<>();
            for (int i = 0; i < columnCount; i++) {
                String columnName = rsmd.getColumnName(i + 1);
                Object columnValue = rs.getObject(i + 1);
                row.put(columnName, columnValue);
            }
            return row;
        }
    }

    /**
     * 查询多行数据, 返回List<T>集合, T 为实体类对象
     */
    public static <T> List<T> selectListBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询单行数据, 返回T对象, T 为实体类对象
     */
    public static <T> T selectOneBean(String sql, Class<T> cls, Object... params) throws SQLException {
        return null;
    }

    /**
     * 查询分页数据
     */
    public static Page<Map<String, Object>> selectPage(String sql, int page, int size, Object... params) throws SQLException {
        //分页结果需要2个数据
        //1.当前页结果集List<Map>
        //2.当行数

        //构建分页对象
        Page<Map<String,Object>> ret = new Page<>(page,size);

        String pageSql = "select a.*,rownum rn from ("+sql+") a where rownum <= ?) a\n" + "where a.rn >?";
        int begin = (page -1)*size;
        int end = begin+size;

        Object[ ] newParams = new Objects[params.length +2];
        //复制参数数组到新数组
        System.arraycopy(params,0,newParams,0,params.length);
        //默认分页参数
        newParams[params.length] = end;
        newParams[params.length+1] = begin;

        List<Map<String,Object>> date = DBHelper.selectListMap(pageSql,newParams);

        //查询不分页的单行数
        String countSql = "select count(*) cnt from ("+sql+")";
        Map<String,Object> map = DBHelper.selectOneMap(countSql,params);
        int rows = Integer.parseInt(""+map.get("CNT"));

        //设置分页对象中
        ret.setData(date,rows);
        return ret;

    }

    /**
     * 事务模板方法
     */
    public static <T> T execute(SqlHandler handler) {
     Connection conn = getConnection();
     try{
         conn.setAutoCommit(false);
         handler.handle(conn);
         //如果成功提交
         conn.commit();
     }catch (Exception e){
         try{
             conn.rollback();
         }catch (SQLException e1){
             throw new RuntimeException("事务回滚失败",e1);
         }
         e.printStackTrace();
     }finally{
         try{
             conn.close();
         }catch (SQLException e){
             throw new RuntimeException("关闭连接失败",e);
         }
     }
     return null;

    }

    /**
     * 执行SQL语句的 函数接口
     */
    public static interface SqlHandler<T> {
        T handle(Connection connection);
    }

    public static class Page<E> {

        /**
         * 当前页数
         */
        private int page = 1;
        /**
         * 每页行数
         */
        private int size = 10;
        /**
         * 结果集总记录数
         */
        private int rows;
        /**
         * 结果集总页数
         */
        private int pages;
        /**
         * 查询到当前页的数据
         */
        private List<E> data;

        public Page(int page, int size) {
            this.page = page;
            this.size = size;
        }

        public void setData(List<E> data, int rows) {
            this.data = data;
            this.rows = rows;
            // 计算总页数
            this.pages = rows / size;
            pages += rows % size == 0 ? 0 : 1;
        }

        /**
         * 返回前后页的页码
         *
         * @return
         */
        public int nextPage() {
            return page == pages ? page : (page + 1);
        }

        /**
         * 返回前一页的页码
         *
         * @return
         */
        public int priorPage() {
            return page == 1 ? page : (page - 1);
        }

        public int getPage() {
            return page;
        }

        public int getSize() {
            return size;
        }

        public int getRows() {
            return rows;
        }

        public int getPages() {
            return pages;
        }

        public List<E> getData() {
            return data;
        }
    }

}


